import datetime
import os

import openpyxl
import pandas as pd
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.workbook import Workbook

# 获取当前工作目录
current_directory = os.getcwd()

folder_path = os.path.join(current_directory, '1_2000')

def create_formatted_sheet(df,num_rows):
    # 创建一个工作簿
    workbook = Workbook()
    # 选择一个表格 (默认为第一个)
    sheet = workbook.active
    sheet.merge_cells('A1:E1')
    # 合并H2到P2
    sheet.merge_cells('H2:P10')

    # 设置合并后单元格的对齐方式
    cell = sheet['A1']
    cell.alignment = Alignment(horizontal='center', vertical='center')
    sheet['H2'].alignment = Alignment(horizontal='center', vertical='center', wrapText=True)

    for col_idx in range(1, 7):  # A列的索引是1，F列的索引是6
        column_letter = get_column_letter(col_idx)
        sheet.column_dimensions[column_letter].width = 25.0  # 直接设置宽度

    # 设置A2到F9的单元格居中对齐
    for row in range(2, num_rows + 3):  # 行号从2到9
        for col in range(1, 7):  # 列号从1到6，A列是第1列，F列是第6列
            sheet.cell(row=row, column=col).alignment = Alignment(horizontal='center', vertical='center')

    # 设置第1到第5行的高度
    for row_idx in range(1, num_rows + 3):  # 第一行的索引是1，第五行的索引是5
        sheet.row_dimensions[row_idx].height = 50  # 直接设置高度

    # 创建字体对象，设置加粗和颜色
    bold_font = Font(name='Calibri', bold=True)  # 加粗字体
    red_bold_font = Font(name='Calibri', bold=True, color='FF0000')  # 加粗字体，颜色为红色

    # 创建填充对象，设置背景色
    fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')  # 背景颜色为黄色

    # A1赋予字体加粗
    sheet['A1'].font = bold_font

    # A2:E2字体加粗
    coordinates = ['A2', 'B2', 'C2', 'D2', 'E2']
    for coord in coordinates:
        cell = sheet[coord]
        cell.font = bold_font

    # E2字体加粗，背景黄色
    sheet['H2'].font = red_bold_font
    sheet['H2'].fill = fill

    sheet['A1'] = '结算单明细'
    sheet['A2'] = '自由职业者姓名'
    sheet['B2'] = '自由职业者身份证号'
    sheet['C2'] = '银行卡号/支付宝帐号'
    sheet['D2'] = '结算金额(元)（结算金额不含服务费）'
    sheet['E2'] = '备注'
    sheet[
        'H2'] = '注意事项：\n1、收款银行过于小众可能会支付失败（如：村镇银行等），请联系自由职业者更换收款账户\n 2、自由职业者收款账户为支付宝的，目前仅支持企业银行账户为平安-支付宝、网商银行进行支付，其余银行账户可能会支付失败，支付前请核对企业账户类型 \n3、前5列必填，请勿修改/删除/增加；备注列非必填且仅支持中文、大小写英文字母、逗号、句号、数字、中划线、下划线，其他字符暂不支持 \n4、自由职业者发放金额超过单笔最大限额时，系统会自动拆分成多笔进行发放(单笔最大限额：平安银行50000，平安-微信20000，建设银行50000) \n5、修改文件名时请勿修改/删除后缀名'

    # for col_letter in ['C']:  # 假定银行卡号在C列，根据实际情况调整
    #     col_idx = ord(col_letter) - ord('A') + 1  # 计算列索引
    #     sheet.column_dimensions[get_column_letter(col_idx)].number_format = '@'  # 设置为文本格式

    df['银行卡号/支付宝帐号'] = df['银行卡号/支付宝帐号'].astype(str)
    df['自由职业者身份证号'] = df['自由职业者身份证号'].astype(str)

    # 将DataFrame内容写入工作表，从num_rows - 3行开始
    start_row_index = 3  # 或者直接使用num_rows - 3，根据你的逻辑来
    for idx, r in enumerate(dataframe_to_rows(df, index=False, header=False), start=start_row_index):
        for col_idx, cell_value in enumerate(r, start=1):
            sheet.cell(row=idx, column=col_idx).value = cell_value
    # 如果一行有多列数据，可以循环r来分别赋值给对应列


    # # 自动调整列宽以适应内容
    # for col in sheet.columns:
    #     sheet.column_dimensions[col[0].column].auto_fit = True

    return workbook, sheet

# 遍历文件夹中的Excel文件
for filename in os.listdir(folder_path):

    if filename.endswith('.xlsx') or filename.endswith('.xls'):
        # 完整文件路径
        file_path = os.path.join(folder_path, filename)

        # 读取数据但不保留格式
        column_names = ['自由职业者姓名', '自由职业者身份证号','自由职业者手机号', '银行卡号/支付宝帐号', '结算金额(元)（结算金额不含服务费）', '备注']
        df = pd.read_excel(file_path, engine='openpyxl',usecols=column_names,header=1)
        num_rows = df.shape[0]

        # 删除指定列
        column_to_remove = '自由职业者手机号'
        print(df.columns)
        if column_to_remove in df.columns:
            df.drop(columns=[column_to_remove], inplace=True)

        # 创建新的工作簿
        # 创建新的工作簿和格式化工作表
        workbook, sheet = create_formatted_sheet(df, num_rows)

        # 保存到新的Excel文件
        new_file_path = os.path.join(folder_path,'处理后的数据')
        if not os.path.exists(new_file_path):
            os.makedirs(new_file_path)
        basename, extension = os.path.splitext(filename)
        final_file_path = os.path.join(new_file_path,f'{basename}_处理后的数据.xlsx')
        workbook.save(final_file_path)
        print(f'{filename} has been processed and saved as backup.')
    else:
        print(f'Skipping non-Excel file: {filename}')

print('All files processed.')

